/**
 * 检查玩家副将数量脚本
 * 快速检查当前数据库中玩家副将的分布情况
 */

const mysql = require('mysql2/promise');

// 数据库配置
const DB_CONFIG = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4',
    timezone: '+08:00'
};

async function checkDeputyCounts() {
    let connection;

    try {
        console.log('🔗 连接数据库...');
        connection = await mysql.createConnection(DB_CONFIG);
        console.log('✅ 数据库连接成功');

        // 1. 检查玩家副将表的基本统计
        console.log('\n📊 玩家副将表统计:');
        const [playerDeputyStats] = await connection.execute(`
            SELECT 
                COUNT(*) as total_records,
                COUNT(DISTINCT player_id) as unique_players,
                MIN(created_at) as earliest_record,
                MAX(created_at) as latest_record
            FROM player_deputy
        `);

        console.log(`   总记录数: ${playerDeputyStats[0].total_records}`);
        console.log(`   唯一玩家数: ${playerDeputyStats[0].unique_players}`);
        console.log(`   最早记录: ${playerDeputyStats[0].earliest_record}`);
        console.log(`   最新记录: ${playerDeputyStats[0].latest_record}`);

        // 2. 检查副将数量分布
        console.log('\n📈 副将数量分布:');
        const [distribution] = await connection.execute(`
            SELECT 
                deputy_count,
                COUNT(*) as player_count
            FROM (
                SELECT 
                    player_id,
                    COUNT(*) as deputy_count
                FROM player_deputy 
                GROUP BY player_id
            ) as counts
            GROUP BY deputy_count
            ORDER BY deputy_count DESC
        `);

        distribution.forEach(row => {
            const status = row.deputy_count > 10 ? '❌ 需要迁移' : '✅ 正常';
            console.log(`   ${row.deputy_count}个副将: ${row.player_count}个玩家 ${status}`);
        });

        // 3. 检查超过10个副将的玩家详情
        console.log('\n🔍 超过10个副将的玩家详情:');
        const [excessPlayers] = await connection.execute(`
            SELECT 
                player_id,
                COUNT(*) as deputy_count,
                MIN(created_at) as first_deputy,
                MAX(created_at) as last_deputy
            FROM player_deputy 
            GROUP BY player_id 
            HAVING COUNT(*) > 10
            ORDER BY deputy_count DESC
        `);

        if (excessPlayers.length === 0) {
            console.log('   ✅ 没有玩家超过10个副将，数据正常');
        } else {
            console.log(`   ❌ 发现 ${excessPlayers.length} 个玩家超过10个副将:`);
            excessPlayers.forEach(player => {
                console.log(`      玩家ID: ${player.player_id}, 副将数: ${player.deputy_count}, 时间范围: ${player.first_deputy} ~ ${player.last_deputy}`);
            });
        }

        // 4. 检查仓库表状态
        console.log('\n🏪 仓库表状态:');
        const [warehouseStats] = await connection.execute(`
            SELECT 
                COUNT(*) as total_records,
                COUNT(DISTINCT player_id) as unique_players,
                MIN(created_at) as earliest_record,
                MAX(created_at) as latest_record
            FROM player_deputy_warehouse
        `);

        console.log(`   总记录数: ${warehouseStats[0].total_records}`);
        console.log(`   唯一玩家数: ${warehouseStats[0].unique_players}`);
        console.log(`   最早记录: ${warehouseStats[0].earliest_record}`);
        console.log(`   最新记录: ${warehouseStats[0].latest_record}`);

        // 5. 检查是否有重复的副将（同时存在于两个表中）
        console.log('\n🔍 检查数据一致性:');
        const [duplicates] = await connection.execute(`
            SELECT 
                pd.player_id,
                pd.deputy_id,
                pd.created_at as player_deputy_created,
                pw.created_at as warehouse_created
            FROM player_deputy pd
            INNER JOIN player_deputy_warehouse pw 
                ON pd.player_id = pw.player_id 
                AND pd.deputy_id = pw.deputy_id
        `);

        if (duplicates.length === 0) {
            console.log('   ✅ 没有发现重复的副将记录');
        } else {
            console.log(`   ❌ 发现 ${duplicates.length} 个重复的副将记录:`);
            duplicates.forEach(dup => {
                console.log(`      玩家ID: ${dup.player_id}, 副将ID: ${dup.deputy_id}`);
            });
        }

        // 6. 总结
        console.log('\n📋 总结:');
        const totalExcess = excessPlayers.reduce((sum, player) => sum + (player.deputy_count - 10), 0);
        console.log(`   需要迁移的副将总数: ${totalExcess}`);
        console.log(`   需要迁移的玩家数: ${excessPlayers.length}`);

        if (excessPlayers.length > 0) {
            console.log('\n⚠️ 建议执行迁移脚本: node migrate-deputy-warehouse-data.js');
        } else {
            console.log('\n✅ 数据状态正常，无需迁移');
        }

    } catch (error) {
        console.error('❌ 检查过程中发生错误:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('\n🔌 数据库连接已关闭');
        }
    }
}

// 运行检查
checkDeputyCounts().catch(console.error);
